package com.Xht.www.dao;

import com.Xht.www.po.RoomTypeInfoPO;
import com.Xht.www.po.SalesVolumeInfoPO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class RoomTypeInfoDAO extends BaseDAO {

    private static final Logger LOGGER = Logger.getLogger(RoomTypeInfoDAO.class.getName());
    private SalesVolumeInfoDAO salesDAO = new SalesVolumeInfoDAO();
    private TurnoverInfoDAO turnoverDAO = new TurnoverInfoDAO(); // 新增营业额DAO

    //插入房型信息并更新营业额，销量
    public void insertRoomTypeInfo(RoomTypeInfoPO roomTypeInfoPO) throws SQLException {
        Connection connection = null;
        boolean success = false;

        try {
            connection = getConnection();
            connection.setAutoCommit(false);

            // 1. 插入房型信息
            String sql = "INSERT INTO room_type_info (merchant_name, bed_type, remaining_quantity, keywords, price, sales_volume) VALUES (?,?,?,?,?,?)";
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
                preparedStatement.setString(1, roomTypeInfoPO.getMerchantName());
                preparedStatement.setString(2, roomTypeInfoPO.getBedType());
                preparedStatement.setInt(3, roomTypeInfoPO.getRemainingQuantity());
                preparedStatement.setString(4, roomTypeInfoPO.getKeywords());
                preparedStatement.setBigDecimal(5, roomTypeInfoPO.getPrice());
                preparedStatement.setInt(6, roomTypeInfoPO.getSalesVolume());

                preparedStatement.executeUpdate();
                success = true;
            }

            // 2. 更新销量表和营业额
            if (success) {
                updateSalesVolumeByMerchant(connection, roomTypeInfoPO.getMerchantName());
                turnoverDAO.updateTurnoverByMerchant(connection, roomTypeInfoPO.getMerchantName()); // 更新营业额
            }

            connection.commit();
        } catch (SQLException e) {
            if (connection != null) {
                connection.rollback();
            }
            throw e;
        } finally {
            if (connection != null) {
                connection.setAutoCommit(true);
                releaseConnection(connection);
            }
        }
    }

    // 查询指定商家的所有房型信息
    public List<RoomTypeInfoPO> queryRoomTypeInfoByMerchantName(String merchantName) throws SQLException {
        List<RoomTypeInfoPO> roomTypeInfoList = new ArrayList<>();
        String sql = "SELECT * FROM room_type_info WHERE merchant_name = ?";
        Connection connection = getConnection();

        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setString(1, merchantName);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                RoomTypeInfoPO roomTypeInfo = new RoomTypeInfoPO();
                roomTypeInfo.setRoomTypeId(resultSet.getInt("room_type_id"));
                roomTypeInfo.setMerchantName(resultSet.getString("merchant_name"));
                roomTypeInfo.setBedType(resultSet.getString("bed_type"));
                roomTypeInfo.setRemainingQuantity(resultSet.getInt("remaining_quantity"));
                roomTypeInfo.setKeywords(resultSet.getString("keywords"));
                roomTypeInfo.setPrice(resultSet.getBigDecimal("price"));
                roomTypeInfo.setSalesVolume(resultSet.getInt("sales_volume"));
                roomTypeInfoList.add(roomTypeInfo);
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            if (connection != null) {
                releaseConnection(connection);
            }
        }
        return roomTypeInfoList;
    }

    // 删除房型信息并更新营业额，销量
    public void deleteRoomTypeInfo(int roomTypeId) throws SQLException {
        Connection connection = null;
        RoomTypeInfoPO roomTypeInfo = null;

        try {
            connection = getConnection();
            connection.setAutoCommit(false);

            // 1. 查询要删除的房型信息（用于获取商家和床型）
            roomTypeInfo = queryRoomTypeInfoByRoomTypeId(roomTypeId);

            // 2. 删除房型信息
            String sql = "DELETE FROM room_type_info WHERE room_type_id = ?";
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
                preparedStatement.setInt(1, roomTypeId);
                preparedStatement.executeUpdate();
            }

            // 3. 更新销量表和营业额
            if (roomTypeInfo != null) {
                updateSalesVolumeByMerchant(connection, roomTypeInfo.getMerchantName());
                turnoverDAO.updateTurnoverByMerchant(connection, roomTypeInfo.getMerchantName()); // 更新营业额
            }

            connection.commit();
        } catch (SQLException e) {
            if (connection != null) {
                connection.rollback();
            }
            throw e;
        } finally {
            if (connection != null) {
                connection.setAutoCommit(true);
                releaseConnection(connection);
            }
        }
    }


    // 更新房型信息并更新营业额，销量
    public boolean updateRoomTypeInfo(RoomTypeInfoPO roomTypeInfoPO) throws SQLException {
        Connection connection = null;
        boolean success = false;

        try {
            connection = getConnection();
            connection.setAutoCommit(false);

            // 1. 更新房型信息
            String sql = "UPDATE room_type_info SET bed_type = ?, remaining_quantity = ?, keywords = ?, price = ?, sales_volume = ? WHERE room_type_id = ?";
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
                preparedStatement.setString(1, roomTypeInfoPO.getBedType());
                preparedStatement.setInt(2, roomTypeInfoPO.getRemainingQuantity());
                preparedStatement.setString(3, roomTypeInfoPO.getKeywords());
                preparedStatement.setBigDecimal(4, roomTypeInfoPO.getPrice());
                preparedStatement.setInt(5, roomTypeInfoPO.getSalesVolume());
                preparedStatement.setInt(6, roomTypeInfoPO.getRoomTypeId());

                int rows = preparedStatement.executeUpdate();
                success = rows > 0;
            }

            // 2. 更新销量表和营业额
            if (success) {
                updateSalesVolumeByMerchant(connection, roomTypeInfoPO.getMerchantName());
                turnoverDAO.updateTurnoverByMerchant(connection, roomTypeInfoPO.getMerchantName()); // 更新营业额
            }

            connection.commit();
            return success;
        } catch (SQLException e) {
            if (connection != null) {
                connection.rollback();
            }
            throw e;
        } finally {
            if (connection != null) {
                connection.setAutoCommit(true);
                releaseConnection(connection);
            }
        }
    }

    // 根据房型ID查询房型信息
    public RoomTypeInfoPO queryRoomTypeInfoByRoomTypeId(int roomTypeId) {
        RoomTypeInfoPO roomTypeInfo = null;
        String sql = "SELECT * FROM room_type_info WHERE room_type_id = ?";
        Connection connection = getConnection();

        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setInt(1, roomTypeId);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    roomTypeInfo = new RoomTypeInfoPO();
                    roomTypeInfo.setRoomTypeId(rs.getInt("room_type_id"));
                    roomTypeInfo.setMerchantName(rs.getString("merchant_name"));
                    roomTypeInfo.setBedType(rs.getString("bed_type"));
                    roomTypeInfo.setRemainingQuantity(rs.getInt("remaining_quantity"));
                    roomTypeInfo.setKeywords(rs.getString("keywords"));
                    roomTypeInfo.setPrice(rs.getBigDecimal("price"));
                    roomTypeInfo.setSalesVolume(rs.getInt("sales_volume"));
                }
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "根据房型ID查询房型信息失败", e);
        } finally {
            releaseConnection(connection);
        }
        return roomTypeInfo;
    }

    // 根据商家名称和床型查询房型信息
    public RoomTypeInfoPO queryRoomTypeInfoByMerchantNameAndBedType(String merchantName, String bedType) {
        RoomTypeInfoPO roomTypeInfo = null;
        String sql = "SELECT * FROM room_type_info WHERE merchant_name = ? AND bed_type = ?";
        Connection connection = getConnection();

        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, merchantName);
            pstmt.setString(2, bedType);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    roomTypeInfo = new RoomTypeInfoPO();
                    roomTypeInfo.setRoomTypeId(rs.getInt("room_type_id"));
                    roomTypeInfo.setMerchantName(rs.getString("merchant_name"));
                    roomTypeInfo.setBedType(rs.getString("bed_type"));
                    roomTypeInfo.setRemainingQuantity(rs.getInt("remaining_quantity"));
                    roomTypeInfo.setKeywords(rs.getString("keywords"));
                    roomTypeInfo.setPrice(rs.getBigDecimal("price"));
                    roomTypeInfo.setSalesVolume(rs.getInt("sales_volume"));
                }
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "根据商家名称和床型查询房型信息失败", e);
        } finally {
            releaseConnection(connection);
        }

        return roomTypeInfo;
    }

    // 根据商家更新销量表
    private void updateSalesVolumeByMerchant(Connection connection, String merchantName) throws SQLException {
        // 查询该商家的总销量
        String sumSql = "SELECT SUM(sales_volume) FROM room_type_info WHERE merchant_name = ?";
        try (PreparedStatement sumStmt = connection.prepareStatement(sumSql)) {
            sumStmt.setString(1, merchantName);

            try (ResultSet rs = sumStmt.executeQuery()) {
                if (rs.next()) {
                    int totalSales = rs.getInt(1);

                    // 检查销量表是否已有记录
                    boolean exists = checkSalesRecordExists(connection, merchantName);

                    if (exists) {
                        // 更新现有记录
                        salesDAO.updateTotalSalesVolume(connection, merchantName, totalSales);
                    } else {
                        // 插入新记录
                        SalesVolumeInfoPO record = new SalesVolumeInfoPO();
                        record.setMerchantName(merchantName);
                        record.setQuantitySold(totalSales);
                        salesDAO.insertSalesRecord(connection, record);
                    }
                }
            }
        }
    }

    // 检查销量表中是否已有记录
    private boolean checkSalesRecordExists(Connection connection, String merchantName) throws SQLException {
        String sql = "SELECT COUNT(*) FROM sales_volume_info WHERE merchant_name = ?";
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, merchantName);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) > 0;
                }
            }
        }
        return false;
    }
}
